﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RFIDStockManagement.Model.Public;
using System.Data.Common;
using RFIDStockManagement.Common.Public.Enums;
using System.Data;
using RFIDStockManagement.Common.Public;
using System.Data.SqlClient;
using RFIDStockManagement.IDAL;

namespace RFIDStockManagement.DAL
{
    sealed class ItemAccess : BasicAccess, IBasicInfoAccess
    {
        public ItemAccess()
            : base("rfid_Item")
        {
        }

        protected override BasicModelInfo CreateModelInfo(DbDataReader reader)
        {
            ItemInfo info = null;
            if (reader.Read())
            {
                info = new ItemInfo();
                int ordinal = reader.GetOrdinal("FID");
                info.Id = reader.GetInt64(ordinal);
                ordinal = reader.GetOrdinal("FName");
                info.Name = reader.GetString(ordinal);
                ordinal = reader.GetOrdinal("FNumber");
                info.Number = reader.GetString(ordinal);
                ordinal = reader.GetOrdinal("FBatchNo");
                info.BatchNo = reader.GetString(ordinal);
                ordinal = reader.GetOrdinal("FBaseRfid");
                info.BaseRfid = reader.GetString(ordinal);
                ordinal = reader.GetOrdinal("FValidMonth");
                info.ValidMonth = reader.GetInt32(ordinal);
                ordinal = reader.GetOrdinal("FType");
                info.Type = (EnumItemType)reader.GetInt32(ordinal);
                ordinal = reader.GetOrdinal("FStockId");
                info.StockId = reader.GetInt64(ordinal);
                ordinal = reader.GetOrdinal("FStockPlaceId");
                info.StockPlaceId = reader.GetInt64(ordinal);
                ordinal = reader.GetOrdinal("FMeasureGroupId");
                info.MeasureGroupId = reader.GetInt64(ordinal);
                ordinal = reader.GetOrdinal("FStockMeasureId");
                info.StockMeasureId = reader.GetInt64(ordinal);
                ordinal = reader.GetOrdinal("FSafeStock");
                info.SafeStock = reader.GetDecimal(ordinal);
            }

            return info;
        }

        /// <summary>
        /// 设置被引用信息
        /// </summary>
        /// <param name="info"></param>
        protected override void SetBeQuoteInfo(BasicModelInfo info)
        {
            //如果供应商已经被入库单使用,不允许删除
            DbHelper helper = new DbHelper();
            string sql = string.Format(@"SELECT TOP 1 1 FROM rfid_InStockbillEntry WHERE FItemID={0}
UNION ALL
SELECT TOP 1 1 FROM rfid_OutStockbillEntry WHERE FItemID={0}
", info.Id); ;
            DbCommand comm = helper.GetSqlStringCommand(sql);
            object scalar = helper.ExecuteScalar(comm);
            if (scalar != null)
            {
                info.BeQuote = true;
                info.BeQuoteMessage = "已被出入库单引用";
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="info"></param>
        public override BasicModelInfo Save(BasicModelInfo info)
        {
            DbHelper helper = new DbHelper();
            string sql = string.Empty;
            ItemInfo itemInfo = info as ItemInfo;

            sql = string.Format("SELECT TOP 1 1 FROM rfid_Item WHERE FID<>{0} AND FNumber='{1}'", itemInfo.Id, itemInfo.Number);
            DbCommand comm = helper.GetSqlStringCommand(sql);
            object scalar = helper.ExecuteScalar(comm);
            if (scalar != null)
            {
                throw new Exception(string.Format("物料编码“{0}”已经存在！", itemInfo.Number));
            }

            if (itemInfo.Id <= 0)
            {

                sql = string.Format(@"INSERT INTO rfid_Item(FName,FNumber,FBatchNo,FValidMonth,FStockID,
FStockPlaceID,FMeasureGroupID,FStockMeasureID,
FType,FBaseRfid,FSafeStock)VALUES('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}',{10});
UPDATE rfid_Item SET FBaseRfid=@@IDENTITY WHERE FID=@@IDENTITY
SELECT @@IDENTITY",
                itemInfo.Name, itemInfo.Number, itemInfo.BatchNo, itemInfo.ValidMonth, itemInfo.StockId,
                itemInfo.StockPlaceId, itemInfo.MeasureGroupId, itemInfo.StockMeasureId,
                (int)itemInfo.Type, itemInfo.BaseRfid, itemInfo.SafeStock);
                comm = helper.GetSqlStringCommand(sql);
                scalar = helper.ExecuteScalar(comm);
                if (scalar != null)
                {
                    long id = 0;
                    long.TryParse(scalar.ToString(), out id);
                    itemInfo.Id = id;
                }
            }
            else
            {
                sql = string.Format(@"UPDATE rfid_Item SET FName='{0}',FNumber='{1}',FBatchNo='{2}',FValidMonth={3},FStockID={4},
FStockPlaceID={5},FMeasureGroupID={6},FStockMeasureID={7},
FType={8},FBaseRfid='{9}',FSafeStock={10} WHERE FID={11}",
                    itemInfo.Name, itemInfo.Number, itemInfo.BatchNo, itemInfo.ValidMonth, itemInfo.StockId,
                itemInfo.StockPlaceId, itemInfo.MeasureGroupId, itemInfo.StockMeasureId,
                (int)itemInfo.Type, itemInfo.BaseRfid, itemInfo.SafeStock, itemInfo.Id);
                comm = helper.GetSqlStringCommand(sql);
                helper.ExecuteNonQuery(comm);
            }

            return itemInfo;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="lstInfo"></param>
        public override List<string> Delete(List<string> lstInfo)
        {
            //如果供应商已经被入库单使用,不允许删除
            List<string> result = new List<string>();
            string sql = string.Format(@"SELECT item.FID,item.FName,item.FNumber FROM rfid_Item item WHERE
item.FID IN({0}) AND (EXISTS(SELECT TOP 1 1 FROM rfid_InStockbillEntry stock WHERE item.FID=stock.FItemID)
OR EXISTS(SELECT TOP 1 1 FROM rfid_OutStockbillEntry stock WHERE item.FID=stock.FItemID))", string.Join(",", lstInfo.ToArray()));
            DbHelper helper = new DbHelper();
            DbCommand comm = helper.GetSqlStringCommand(sql);
            using (DbDataReader reader = helper.ExecuteReader(comm))
            {
                while (reader.Read())
                {
                    lstInfo.Remove(reader.GetInt64(reader.GetOrdinal("FID")).ToString());
                    result.Add(string.Format("“{0}（{1}）”已被出入库单引用，不能删除", reader.GetString(reader.GetOrdinal("FName")), reader.GetString(reader.GetOrdinal("FNumber"))));
                }
            }
            if (lstInfo.Count > 0)
            {
                sql = string.Format("DELETE rfid_Item WHERE FID IN({0})", string.Join(",", lstInfo.ToArray()));
                comm = helper.GetSqlStringCommand(sql);
                helper.ExecuteNonQuery(comm);
                if (result.Count == 0)
                {
                    result.Add("所选的数据全部删除成功");
                }
                else
                {
                    result.Add("其它所选的数据删除成功");
                }
            }

            return result;
        }
        /// <summary>
        /// 获取字段列表
        /// </summary>
        /// <returns></returns>
        protected override string GetFieldList()
        {
            return @"item.FID,item.FName,item.FNumber,item.FBatchNo,item.FValidMonth,stock.FName FStockName,item.FStockPlaceID,place.FName FStockPlaceName,
item.FMeasureGroupID,mGroup.FName FMeasureGroupName,item.FStockMeasureID, measure.FName FStockMeasureName,item.FType,item.FSafeStock,item.FBaseRfid";
        }
        /// <summary>
        /// 模糊资料列表
        /// </summary>
        /// <param name="dctFilter"></param>
        /// <returns></returns>
        public override DataTable Search(Dictionary<string, object> dctFilter, bool isAnd)
        {
            DbHelper helper = new DbHelper();

            string strFilter = string.Empty;
            string sql = string.Empty;
            string strTable = @"rfid_Item item LEFT JOIN rfid_Stock stock ON item.FStockID=stock.FID
LEFT JOIN rfid_StockPlace place ON item.FStockPlaceID=place.FID
LEFT JOIN rfid_MeasureGroup mGroup ON item.FMeasureGroupID=mGroup.FID
LEFT JOIN rfid_Measure measure ON item.FStockMeasureID=measure.FID";
            if (dctFilter != null && dctFilter.Count > 0)
            {
                foreach (KeyValuePair<string, object> filter in dctFilter)
                {
                    strFilter += string.Format(" {0} LIKE '%{1}%' {2}", filter.Key, filter.Value, isAnd ? "AND" : "OR ");
                }
                strFilter = strFilter.Substring(0, strFilter.Length - 3);
                sql = string.Format("SELECT TOP {0} {3} FROM {2} WHERE {1} ORDER BY item.FID DESC",
                    PublicConst.SelectTopCount, strFilter, strTable, GetFieldList());

                DbCommand comm = helper.GetSqlStringCommand(sql);
                return helper.ExecuteDataTable(comm);
            }

            return null;
        }
        /// <summary>
        /// 获取资料列表
        /// </summary>
        /// <param name="dctFilter"></param>
        /// <returns></returns>
        public override DataTable GetList(Dictionary<string, object> dctFilter, bool isAnd)
        {
            DbHelper helper = new DbHelper();

            string strFilter = string.Empty;
            string sql = string.Empty;
            List<SqlParameter> parameter = new List<SqlParameter>();
            string strTable = @"rfid_Item item LEFT JOIN rfid_Stock stock ON item.FStockID=stock.FID
LEFT JOIN rfid_StockPlace place ON item.FStockPlaceID=place.FID
LEFT JOIN rfid_MeasureGroup mGroup ON item.FMeasureGroupID=mGroup.FID
LEFT JOIN rfid_Measure measure ON item.FStockMeasureID=measure.FID";
            if (dctFilter != null && dctFilter.Count > 0)
            {
                foreach (KeyValuePair<string, object> filter in dctFilter)
                {
                    strFilter += string.Format(" {0}=@{0} {1}", filter.Key, isAnd ? "AND" : "OR ");
                    parameter.Add(new SqlParameter("@" + filter.Key, filter.Value));
                }
                strFilter = strFilter.Substring(0, strFilter.Length - 3);
                sql = string.Format("SELECT TOP {0} {3} FROM {2} WHERE {1} ORDER BY FID",
                    PublicConst.SelectTopCount, strFilter, strTable, GetFieldList());
            }
            else
            {
                sql = string.Format("SELECT TOP {0} {2} FROM {1} ORDER BY FID",
                    PublicConst.SelectTopCount, strTable, GetFieldList());
            }

            DbCommand comm = helper.GetSqlStringCommand(sql);
            comm.Parameters.AddRange(parameter.ToArray());

            return helper.ExecuteDataTable(comm);
        }
    }
}
